Figures
Tables
library(tidyverse)
library(leaflet)
library(mapview)
library(glue)
library(sf)
dir_tbl <- "~/Google Drive/projects/nps-ecoquants/data/CHISLandVegetationMonitoringDatabase/tables"
to_lonlat <- function(x, y, crs_str){
st_as_sf(data_frame(x=x, y=y), crs=crs_str, coords = c("x", "y")) %>%
st_transform(crs=4326) %>%
st_coordinates()
}
locations <- read_csv(file.path(dir_tbl, "tbl_Locations.txt")) %>%
mutate(
# fix one location
Y_Coord = ifelse(Location_ID == 1502833764, B_Y_Coord, Y_Coord)) %>%
# TODO: fix other locations
filter(!is.na(X_Coord), !is.na(Y_Coord), !is.na(UTM_Zone)) %>%
mutate(
crs_str = glue("+proj={tolower(Coord_System)} +zone={UTM_Zone} +datum={Datum} +units={c(meters='m')[Coord_Units]}"),
lon_lat = pmap(list(X_Coord, Y_Coord, crs_str), to_lonlat),
lon = map_dbl(lon_lat, ~.[,1]),
lat = map_dbl(lon_lat, ~.[,2])) %>%
st_as_sf(coords = c("lon", "lat"), crs=4326)
mapView(locations)
leaflet(locations) %>%
addProviderTiles(providers$Esri.OceanBasemap) %>%
addMarkers(
clusterOptions = markerClusterOptions(),
popup = ~glue(
"<b>Location_ID</b>: {Location_ID}<br>
<b>Vegetation_Community</b>: {Vegetation_Community}<br>
<b>Loc_Notes</b>: {Loc_Notes}"))
library(readxl)
library(here)
dir_qry <- "~/Google Drive/projects/nps-ecoquants/data/CHISLandVegetationMonitoringDatabase/queries"
fig_codes_csv <- here("data/figure_codes.csv")
q_xlsx <- file.path(dir_qry, "qry-2016_fig-E2-absolute-cover.xlsx")
park <- "CHIS"
year <- 2016
veg_communities <- c("Coastal Sage Scrub", "Island Chaparral", "Valley/Foothill Grassland")
q <- readxl::read_excel(q_xlsx, na = c("", "NA")) %>%
arrange(Park, SurveyYear, IslandCode, Vegetation_Community, FxnGroup, Nativity)
# View(q)
# table(q$IslandCode)
# table(q$Vegetation_Community)
# table(q$FxnGroup)
d <- q %>%
filter(
!is.na(Vegetation_Community), !is.na(FxnGroup), !is.na(Average), !is.na(Nativity)) %>%
# NOTE: aggregating across islands
group_by(SurveyYear, Park, Vegetation_Community, FxnGroup, Nativity) %>%
summarize(
pct_avg = mean(Average),
pct_min = mean(MinRange),
pct_max = mean(MaxRange)) %>%
arrange(Vegetation_Community, FxnGroup, Nativity) %>%
filter(Vegetation_Community %in% veg_communities)
# View(d)
#fill=FxnGroup, group=FxnGroup, color=FxnGroup,
g <- ggplot(d, aes(x=Vegetation_Community, y=pct_avg, fill=FxnGroup, alpha=Nativity)) +
#facet_wrap(~Vegetation_Community) +
geom_bar(stat="identity", position="dodge") + # , colour="black"
scale_alpha_manual(values=c(1, 0.3)) +
labs(x="Vegetation Community", y="Cover (%)") #+
#coord_flip()
# TODO: FxnGroup = "All Sites"
g
library(plotly)
ggplotly(g)
library(DT)
library(lubridate)
dir_tbl <- "~/Google Drive/projects/nps-ecoquants/data/CHISLandVegetationMonitoringDatabase/tables"
events <- read_csv(file.path(dir_tbl, "tbl_Events.txt")) # View(events)
event_points <- read_csv(file.path(dir_tbl, "tbl_Event_Point.txt")) # View(event_points)
species_data <- read_csv(file.path(dir_tbl, "tbl_Species_Data.txt")) # View(species_data)
#project_taxa <- read_csv(file.path(dir_tbl, "tlu_Project_Taxa.txt"))
locations <- read_csv(file.path(dir_tbl, "tbl_Locations.txt")) # View(locations)
sites <- read_csv(file.path(dir_tbl, "tbl_Sites.txt")) # View(sites)
d <- events %>%
mutate(
start_date = as.Date(Start_Date, "%m/%d/%Y %H:%M:%S")) %>%
left_join(
event_points, by="Event_ID") %>%
left_join(
species_data, by="Event_Point_ID") %>%
#left_join(
# project_taxa, by=c("Species_Code"="Species_code"))
left_join(
locations, by="Location_ID") %>%
left_join(
sites, by="Site_ID") %>%
filter(year(start_date) == 2016) %>%
group_by(Site_Desc, Species_Code) %>%
summarize(
n = n())
datatable(d)
d %>%
group_by(Site_Desc) %>%
summarize(
n_species = n()) %>%
kable(format.args = list(big.mark = ","))
| Site_Desc | n_species |
|---|---|
| Anacapa Island | 28 |
| San Miguel Island | 42 |
| Santa Barbara Island | 30 |
| Santa Cruz Island | 77 |
| Santa Rosa Island | 166 |
Declare Function to 64-bit Private Declare PtrSafe Function using MS Access 2013Windows only connection option:
library(RODBC)
accdb <- "Z:/bbest On My Mac/Google Drive/projects/nps-ecoquants/data/CHISLandVegetationMonitoringDatabase/LandVegetationMonitoring_DATA_be.accdb"
odbcDataSources()
db <- odbcConnectAccess2007(accdb)
sqlTables(db)
sqlQuery("SELECT * FROM tbl_Locations")
Connect Access Front-End to Postgres Backend:
Assistant applications to handle conversion / synchronization:
For example, with Shiny:
Working with databases & Shiny:
From the same Rmarkdown document:
You can generate these (and more):